# 学生历史变动信息管理
import numpy as np
from flask import request, jsonify, Blueprint
from flask_jwt_extended import jwt_required, get_jwt
import exts
import sqlite3
import datetime

info_changes_blueprint = Blueprint('info_changes', __name__)


# 学生历史变动信息新增
@info_changes_blueprint.route('/add', methods=['POST'])
@jwt_required()
@exts.permission_required('info_changes', 'C')
def info_changes_add():
    """学生历史变动信息新增"""
    data = {
        'school_id': request.json.get('school_id', None),
        'student_id': request.json.get('student_id', None),
        'change_type': request.json.get('change_type', None),
        'previous_value': request.json.get('previous_value', None),
        'new_value': request.json.get('new_value', None),
        'change_method': request.json.get('change_method', None),
        'remark': request.json.get('remark', None)
    }
    # 检查必需字段是否为空
    if data['student_id'] is None or data['change_type'] is None or data['previous_value'] is None \
            or data['new_value'] is None or data['change_method'] is None:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "缺少参数",
            "details": "student_id, change_type, previous_value, new_value, change_method 为必需参数"
        }), 400
    # 检查 previous_value 和 new_value 是否为字典类型
    if not isinstance(data['previous_value'], dict) or not isinstance(data['new_value'], dict):
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "参数错误",
            "details": "previous_value 和 new_value 必须为 object 类型"
        }), 400
    # 检查字典的 key 是否为空
    if not data['previous_value'] or not data['new_value']:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "参数错误",
            "details": "previous_value 和 new_value 不能为空"
        }), 400
    # 检查 school_id 是否为空，如果为空，则使用当前用户的 school_id
    if not data['school_id']:
        claims = get_jwt()
        data['school_id'] = claims['school_id']
    # 检查备注是否为空，如果为空，则设置为 '无'
    if not data['remark']:
        data['remark'] = '无'
    # 生成 change_detail 字段和 change_date 字段
    temp = "字段变动 "
    for key, value in data['previous_value'].items():
        temp += f"'{key}' : '{value}' -> '{data['new_value'][key]}'; "
    data['change_detail'] = f"""{data['change_type']}, {temp}"""
    data['change_date'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # 将 previous_value 和 new_value 转换为字符串
    data['previous_value'] = str(data['previous_value'])
    data['new_value'] = str(data['new_value'])

    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        sql = f"""
            INSERT INTO StudentHistoryChange (school_id, student_id, change_type, previous_value, new_value,
                                                change_method, remark, change_detail, change_date)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        """
        cur.execute(sql, (*data.values(),))
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "学生历史变动信息新增成功",
            "data": {
                "change_id": cur.lastrowid
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            exts.close_db(conn)


# 学生历史变动信息删除
@info_changes_blueprint.route('/delete', methods=['DELETE'])
@jwt_required()
@exts.permission_required('info_changes', 'D')
def info_changes_delete():
    """学生历史变动信息删除"""
    data = request.json.get('data', None)
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "未提供有效的记录ID"
        }), 400
    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        result = []
        for change_id in data:
            sql = 'DELETE FROM StudentHistoryChange WHERE change_id = ?'
            cur.execute(sql, (change_id,))
            if cur.rowcount == 0:
                result.append({
                    'change_id': change_id,
                    'status': 'error',
                    'message': '未找到学生变动信息'
                })
            else:
                result.append({
                    'change_id': change_id,
                    'status': 'success',
                    'message': '删除学生变动信息成功'
                })
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "操作成功",
            "data": result
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            exts.close_db(conn)


# 历史变动信息的更新
@info_changes_blueprint.route('/update/<change_id>', methods=['PUT'])
@jwt_required()
@exts.permission_required('info_changes', 'U')
def info_changes_update(change_id):
    """学生历史变动信息更新"""
    valid_params = ['school_id', 'student_id', 'change_type', 'previous_value', 'new_value',
                    'change_date', 'change_method', 'remark']
    required_params = ['student_id', 'change_type', 'previous_value', 'new_value', 'change_method']
    data = {key: request.json.get(key) for key in valid_params}
    # 检查可更新的字段，如果不存在，则删除
    for key in valid_params:
        if key not in request.json:
            data.pop(key)
    # 如果参数为空，则返回 400 错误
    if not data:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "请提供有效的更新字段"
        }), 400
    # 检查必需字段，如果存在，则不允许为空
    for key in required_params:
        if key in request.json and request.json[key] is None:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "请求参数错误",
                "details": f"缺少必要的参数, 请确保 {key} 参数不为空"
            }), 400
    # 检查 previous_value 和 new_value 是否为字典类型
    if 'previous_value' in data and 'new_value' in data:
        if not isinstance(data['previous_value'], dict) or not isinstance(data['new_value'], dict):
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "参数错误",
                "details": "previous_value 和 new_value 必须为字典类型"
            }), 400
    # 检查字典的 key 是否为空
    if 'previous_value' in data and 'new_value' in data:
        if not data['previous_value'] or not data['new_value']:
            return jsonify({
                "code": 400,
                "status": "error",
                "message": "参数错误",
                "details": "previous_value 和 new_value 不能为空"
            }), 400
    # 检查 school_id 是否为空，如果为空，则使用当前用户的 school_id
    if 'school_id' in data and not data['school_id']:
        claims = get_jwt()
        data['school_id'] = claims['school_id']
    # 检查 change_date 是否为空，如果为空，则自动生成
    if 'change_date' in data and not data['change_date']:
        data['change_date'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    # 生成 change_detail 字段
    if 'previous_value' in data and 'new_value' in data:
        temp = "字段变动 "
        for key, value in data['previous_value'].items():
            temp += f"'{key}' : '{value}' -> '{data['new_value'][key]}'; "
        data['change_detail'] = f"""{data['change_type']}, {temp}"""
    # 检查备注是否为空，如果为空，则设置为 '无'
    if 'remark' in data and not data['remark']:
        data['remark'] = '无'
    # 将 previous_value 和 new_value 转换为字符串
    if 'previous_value' in data:
        data['previous_value'] = str(data['previous_value'])
    if 'new_value' in data:
        data['new_value'] = str(data['new_value'])

    conn = None
    try:
        conn = exts.get_db()
        cur = conn.cursor()
        sql = f"""
            UPDATE StudentHistoryChange
            SET {', '.join([f'{key} = ?' for key in data.keys()])}
            WHERE change_id = ?
        """
        cur.execute(sql, (*data.values(), change_id))
        if cur.rowcount == 0:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到指定的学生历史变动信息",
                "details": f"未找到 change_id 为 {change_id} 的学生历史变动信息"
            }), 404
        conn.commit()
        return jsonify({
            "code": 200,
            "status": "success",
            "message": "学生历史变动信息更新成功",
            "data": {
                "change_id": change_id
            }
        }), 200
    except Exception as e:
        if conn:
            conn.rollback()
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": f"错误详情：{e}"
        }), 500
    finally:
        if conn:
            exts.close_db(conn)


# 历史变动信息的查询
@info_changes_blueprint.route('/query', methods=['GET'])
@jwt_required()
@exts.permission_required('info_changes', 'R')
def info_changes_query():
    """学生历史变动信息查询"""
    school_id = request.args.get('school_id', get_jwt()['school_id'], type=int)
    student_id = request.args.get('student_id', None, type=int)
    student_name = request.args.get('student_name', None, type=str)
    student_grade = request.args.get('student_grade', None, type=str)
    change_type = request.args.get('change_type', None, type=str)
    change_date = request.args.get('change_date', None, type=str)
    change_method = request.args.get('change_method', None, type=str)
    remark = request.args.get('remark', None, type=str)
    page = request.args.get('page', 1, type=int)
    page_size = request.args.get('page_size', 10, type=int)
    if page < 1 or page_size < 1:
        return jsonify({
            "code": 400,
            "status": "error",
            "message": "请求参数错误",
            "details": "page 和 page_size 必须为正整数"
        }), 400

    offset = (page - 1) * page_size

    conditions = [f"shc.school_id = {school_id}"]
    if student_id:
        conditions.append(f"shc.student_id = {student_id}")
    if student_name:
        conditions.append(f"(cs.student_name LIKE '%{student_name}%' OR asr.student_name LIKE '%{student_name}%')")
    if student_grade:
        conditions.append(f"(cs.student_grade LIKE '%{student_grade}%' OR asr.student_grade LIKE '%{student_grade}%')")
    if change_type:
        conditions.append(f"shc.change_type LIKE '%{change_type}%'")
    if change_date:
        conditions.append(f"shc.change_date LIKE '%{change_date}%'")
    if change_method:
        conditions.append(f"shc.change_method LIKE '%{change_method}%'")
    if remark:
        conditions.append(f"shc.remark LIKE '%{remark}%'")

    where_clause = " AND ".join(conditions)

    sql = f"""
        SELECT DISTINCT shc.*, 
                COALESCE(cs.student_name, asr.student_name) AS student_name,
                COALESCE(cs.student_grade, asr.student_grade) AS student_grade,
                s.school_name
        FROM StudentHistoryChange shc
        LEFT JOIN CurrentStudent cs ON shc.student_id = cs.student_id
        LEFT JOIN AllStudentRecord asr ON shc.student_id = asr.student_id
        LEFT JOIN School s ON shc.school_id = s.school_id
        WHERE {where_clause}
        ORDER BY shc.change_id DESC
        LIMIT {page_size} OFFSET {offset}
    """

    sql_count = f"""
        SELECT COUNT(DISTINCT shc.change_id)
        FROM StudentHistoryChange shc
        LEFT JOIN CurrentStudent cs ON shc.student_id = cs.student_id
        LEFT JOIN AllStudentRecord asr ON shc.student_id = asr.student_id
        LEFT JOIN School s ON shc.school_id = s.school_id
        WHERE {where_clause}
    """

    conn = None
    try:
        conn = exts.get_db()
        cursor = conn.cursor()
        # 查询总记录数
        cursor.execute(sql_count)
        total_records = cursor.fetchone()[0]
        total_pages = (total_records + page_size - 1) // page_size
        if 0 < total_pages < page:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "请求的页码超出范围",
                "details": f"请求的页码超出了最大页数（{total_pages}页）"
            }), 404
        # 查询当前页数据
        cursor.execute(sql)
        results = cursor.fetchall()
        if not results:
            return jsonify({
                "code": 404,
                "status": "error",
                "message": "未找到符合条件的学生历史变动信息"
            }), 404

        results = [dict(zip([column[0] for column in cursor.description], row)) for row in results]
        # 对查询结果进行处理
        for result in results:
            result['previous_value'] = eval(result['previous_value'])
            result['new_value'] = eval(result['new_value'])

        return jsonify({
            "code": 200,
            "status": "success",
            "message": "查询成功",
            "data": {
                "total_records": total_records,
                "total_pages": total_pages,
                "current_page": page,
                "page_size": len(results),
                "results": results
            }
        }), 200
    except Exception as e:
        return jsonify({
            "code": 500,
            "status": "error",
            "message": "服务器内部错误",
            "details": str(e)
        }), 500
    finally:
        if conn:
            conn.close()
